package com.geez.bbl.db.type;


import com.geez.bbl.util.Consumer;
import com.geez.bbl.util.Transformer;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author biniam.gebremichael
 * @since 12/7/11
 */
public class Builder {


    String url = "jdbc:mysql://localhost:3306/ovsdb";
    Connection con;
    String language;
    String table;
    List<BblBook> books;
    final Transformer<String, String> verseVisitor;

    public Builder(String language, String table, Transformer<String, String> verseVisitor) {
        this.language = language;
        this.table = table;
        this.verseVisitor = verseVisitor;
    }

    public Builder(String language, String table) {
        this(language,table,new Transformer<String, String> () {

            @Override
            public String transform(String s) {
                return s;
            }
        }) ;
    }

    public Builder(String language) {
        this(language,language+"_bible") ;
    }

    public List<BblBook> build() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        con = DriverManager.getConnection(url, "root",null);
      //  con = DriverManager.getConnection(url, "root", "ensure");
        buildBooks();
        fillContent();
        return books;
    }

    public void fillContent() throws Exception {
        for (BblBook book : books) {
            for (int i = 1; i <= book.getChapterSize(); i++) {
                List<Verse> verses = getVerses(book.getBook_no(), i);
                BblChapter bblChapter = new BblChapter(i, Status.OK, new ArrayList<String>(), verses);
                book.addChapter(i, bblChapter);
            }
        }
    }

    private List<Verse> getVerses(int bookNo, int chapterNo) throws SQLException {
        List<Verse> verses = new ArrayList<Verse>();
        String selectChapter = "SELECT id ,book ,chapter ,no_start ,no_end ,Article ,PARAGRAPH ,IP ,entry_date ,remark " +
                "from TABLE where book = BOOKNUM and chapter= CHAPNUM order by book,chapter,no_start;";
        String query = selectChapter.replace("TABLE",table).replace("BOOKNUM", String.valueOf(bookNo)).replace("CHAPNUM", String.valueOf(chapterNo));
        Statement stmt = con.createStatement();
        stmt.execute(query);
        ResultSet rs = stmt.getResultSet();
        while (rs.next()) {
            int par = rs.getInt("PARAGRAPH");
            verses.add(new Verse(rs.getInt("no_start"), rs.getInt("no_end"),
                    verseVisitor.transform(rs.getString("Article")), VerseStatus.OK, par==1,true));
        }
        stmt.close();
        return verses;
    }


    public void buildBooks() throws Exception {
        String selectBook = " SELECT id ,language ,book_no ,book_type ,long_name ,short_name ,middle_name ,chapter_size ,verse_size " +
                " FROM bible_book_names where language = 'LNG' order by book_no;";
        String query = selectBook.replace("LNG", language);
        books = new ArrayList<BblBook>();
        Statement stmt = con.createStatement();
        stmt.execute(query);
        ResultSet rs = stmt.getResultSet();
        while (rs.next()) {
            BblBook bblBook = new BblBook(rs.getInt("id"), rs.getString("language"), rs.getInt("book_no"), rs.getString("book_type"),
                    rs.getString("long_name"), rs.getString("short_name"), rs.getString("middle_name"),
                    rs.getInt("chapter_size"), rs.getInt("verse_size"));
            books.add(bblBook);
        }
        stmt.close();
    }

}
